<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Build statements without using a parser: GNOME Data Access 5 manual</title>
<meta name="generator" content="DocBook XSL Stylesheets V1.79.1">
<link rel="home" href="index.html" title="GNOME Data Access 5 manual">
<link rel="up" href="howto.html" title="HOWTO for common tasks">
<link rel="prev" href="ch12s02.html" title="Define a data source (DSN)">
<link rel="next" href="howto-exec-select.html" title="Execute a SELECT command">
<meta name="generator" content="GTK-Doc V1.32 (XML mode)">
<link rel="stylesheet" href="style.css" type="text/css">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF">
<table class="navigation" id="top" width="100%" summary="Navigation header" cellpadding="2" cellspacing="5"><tr valign="middle">
<td width="100%" align="left" class="shortcuts"></td>
<td><a accesskey="h" href="index.html"><img src="home.png" width="16" height="16" border="0" alt="Home"></a></td>
<td><a accesskey="u" href="howto.html"><img src="up.png" width="16" height="16" border="0" alt="Up"></a></td>
<td><a accesskey="p" href="ch12s02.html"><img src="left.png" width="16" height="16" border="0" alt="Prev"></a></td>
<td><a accesskey="n" href="howto-exec-select.html"><img src="right.png" width="16" height="16" border="0" alt="Next"></a></td>
</tr></table>
<div class="sect1">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="howto-sqlbuilder"></a>Build statements without using a parser</h2></div></div></div>
<p>
      Statements can be created using a <a class="link" href="GdaSqlParser.html" title="GdaSqlParser">GdaSqlParser</a> object to parse
      SQL strings, but an easier way is to use a <a class="link" href="GdaSqlBuilder.html" title="GdaSqlBuilder">GdaSqlBuilder</a> object
      and the associated APIs to construct the statement. This section gives examples to create
      various statements. Please note that only the DML statements (SELECT, INSERT, UPDATE or DELETE statements
      can be built using a <a class="link" href="GdaSqlBuilder.html" title="GdaSqlBuilder">GdaSqlBuilder</a> object, other types of
      statements can only be built using a parser).
    </p>
<p>
      Each of the examples in this section show the statement construction part, the usage part is not shown for
      clarity reasons (replaced with [...]). Typically one would use the
      <a class="link" href="GdaSqlBuilder.html#gda-sql-builder-get-statement" title="gda_sql_builder_get_statement ()">gda_sql_builder_get_statement()</a> method to
      actually obtain a <a class="link" href="GdaStatement.html" title="GdaStatement">GdaStatement</a> object and execute it.
    </p>
<div class="sect2">
<div class="titlepage"><div><div><h3 class="title">
<a name="id-1.3.3.5.4"></a>INSERT INTO customers (e, f, g) VALUES (##p1::string, 15, 'joe')</h3></div></div></div>
<p>
	</p>
<pre class="programlisting">
GdaSqlBuilder *b;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_INSERT);
gda_sql_builder_set_table (b, "customers");
gda_sql_builder_add_field_value_id (b,
                                    gda_sql_builder_add_id (b, "e"),
                                    gda_sql_builder_add_param (b, "p1", G_TYPE_STRING, FALSE));
gda_sql_builder_add_field_value (b, "f", G_TYPE_INT, 15);
gda_sql_builder_add_field_value (b, "g", G_TYPE_STRING, "joe")
[...]	
g_object_unref (b);	  
	</pre>
<p>
    </p>
</div>
<div class="sect2">
<div class="titlepage"><div><div><h3 class="title">
<a name="id-1.3.3.5.5"></a>SELECT people.firstname AS person, people.lastname, "date" AS birthdate, age FROM people</h3></div></div></div>
<p>
	</p>
<pre class="programlisting">
GdaSqlBuilder *b;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_select_add_field (b, "firstname", "people", "person");
gda_sql_builder_select_add_field (b, "lastname", "people", NULL);
gda_sql_builder_select_add_field (b, "date", NULL, "birthdate");
gda_sql_builder_select_add_field (b, "age", NULL, NULL);
gda_sql_builder_select_add_target_id (b,
				      gda_sql_builder_add_id (b, "people"),
				      NULL);
[...]	
g_object_unref (b);	  
	</pre>
<p>
    </p>
</div>
<div class="sect2">
<div class="titlepage"><div><div><h3 class="title">
<a name="id-1.3.3.5.6"></a>SELECT c."date", name AS person FROM "select" AS c INNER JOIN orders USING (id)</h3></div></div></div>
<p>
	</p>
<pre class="programlisting">
GdaSqlBuilder *b;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
GdaSqlBuilderId id_table = gda_sql_builder_add_id (b, "select"); /* SELECT is an SQL reserved keyword */
GdaSqlBuilderId id_target1 = gda_sql_builder_select_add_target_id (b, id_table, "c");
GdaSqlBuilderId id_target2 = gda_sql_builder_select_add_target_id (b,
                                                                   gda_sql_builder_add_id (b, "orders"),
				                                   NULL);
GdaSqlBuilderId id_join = gda_sql_builder_select_join_targets (b, id_target1, id_target2, GDA_SQL_SELECT_JOIN_INNER, 0);

/* DATE is an SQL reserved keyword */
gda_sql_builder_add_field_value_id (b,
                                    gda_sql_builder_add_id (b, "c.date"), 0);
gda_sql_builder_add_field_value_id (b,
			            gda_sql_builder_add_id (b, "name"),
		  	            gda_sql_builder_add_id (b, "person"));

gda_sql_builder_join_add_field (b, id_join, "id");
[...]	
g_object_unref (b);	  
	</pre>
<p>
    </p>
</div>
<div class="sect2">
<div class="titlepage"><div><div><h3 class="title">
<a name="id-1.3.3.5.7"></a>UPDATE products set ref='A0E''FESP' WHERE id = 14</h3></div></div></div>
<p>
	</p>
<pre class="programlisting">
GdaSqlBuilder *b;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_INSERT);
gda_sql_builder_set_table (b, "products");
gda_sql_builder_add_field_value (b, "ref", G_TYPE_STRING, "A0E'FESP");
GdaSqlBuilderId id_field = gda_sql_builder_add_id (b, "id");
GdaSqlBuilderId id_value = gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 14);
GdaSqlBuilderId id_cond = gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ, id_field, id_value, 0);
gda_sql_builder_set_where (b, id_cond);
[...]	
g_object_unref (b);	  
	</pre>
<p>
    </p>
</div>
<div class="sect2">
<div class="titlepage"><div><div><h3 class="title">
<a name="id-1.3.3.5.8"></a>DELETE FROM items WHERE id = ##theid::int</h3></div></div></div>
<p>
	</p>
<pre class="programlisting">
GdaSqlBuilder *b;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_DELETE);
gda_sql_builder_set_table (b, "items");
GdaSqlBuilderId id_field = gda_sql_builder_add_id (b, "id");
GdaSqlBuilderId id_param = gda_sql_builder_add_param (b, "theid", G_TYPE_INT, FALSE);
GdaSqlBuilderId id_cond = gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ, id_field, id_param, 0);
gda_sql_builder_set_where (b, id_cond);
[...]	
g_object_unref (b);	  
	</pre>
<p>
    </p>
</div>
<div class="sect2">
<div class="titlepage"><div><div><h3 class="title">
<a name="id-1.3.3.5.9"></a>SELECT myfunc (a, 5, 'Joe') FROM mytable</h3></div></div></div>
<p>
	</p>
<pre class="programlisting">
GdaSqlBuilder *b;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_select_add_target_id (b,
				   gda_sql_builder_add_id (b, "mytable"),
				   NULL);
GdaSqlBuilderId id_function = gda_sql_builder_add_function (b, "myfunc",
			            gda_sql_builder_add_id (b, "a"),
			            gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 5),
			            gda_sql_builder_add_expr (b, NULL, G_TYPE_STRING, "Joe"),
			            0);
gda_sql_builder_add_field_value_id (b, id_function, 0);
[...]	
g_object_unref (b);	  
	</pre>
<p>
    </p>
</div>
<div class="sect2">
<div class="titlepage"><div><div><h3 class="title">
<a name="id-1.3.3.5.10"></a>SELECT name FROM master WHERE id IN (SELECT id FROM subdata)</h3></div></div></div>
<p>
	</p>
<pre class="programlisting">
GdaSqlBuilder *b;
GdaSqlStatement *sub;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "id"), 0);
gda_sql_builder_select_add_target_id (b,
				      gda_sql_builder_add_id (b, "subdata"),
				      NULL);
sub = gda_sql_builder_get_sql_statement (b, FALSE);
g_object_unref (b);

b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "name"), 0);
gda_sql_builder_select_add_target_id (b,
				      gda_sql_builder_add_id (b, "master"),
				      NULL);
GdaSqlBuilderId id_field = gda_sql_builder_add_id (b, "id");
GdaSqlBuilderId id_subselect = gda_sql_builder_add_sub_select (b, sub, TRUE);
GdaSqlBuilderId id_cond = gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_IN, id_field, id_subselect, 0);
gda_sql_builder_set_where (b, id_cond);
[...]	
g_object_unref (b);	  
	</pre>
<p>
    </p>
</div>
<div class="sect2">
<div class="titlepage"><div><div><h3 class="title">
<a name="id-1.3.3.5.11"></a>INSERT INTO customers (e, f, g) SELECT id, name, location FROM subdate</h3></div></div></div>
<p>
	</p>
<pre class="programlisting">
GdaSqlBuilder *b;
GdaSqlStatement *sub;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "id"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "name"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "location"), 0);
gda_sql_builder_select_add_target_id (b,
				      gda_sql_builder_add_id (b, "subdate"),
				      NULL);
sub = gda_sql_builder_get_sql_statement (b, FALSE);
g_object_unref (b);

b = gda_sql_builder_new (GDA_SQL_STATEMENT_INSERT);
gda_sql_builder_set_table (b, "customers");
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "e"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "f"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "g"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_sub_select (b, sub, TRUE), 0);
[...]	
g_object_unref (b);	  
	</pre>
<p>
    </p>
</div>
<div class="sect2">
<div class="titlepage"><div><div><h3 class="title">
<a name="id-1.3.3.5.12"></a>SELECT id, name FROM subdata1 UNION SELECT ident, lastname FROM subdata2</h3></div></div></div>
<p>
	</p>
<pre class="programlisting">
GdaSqlBuilder *b;
GdaSqlStatement *sub1, *sub2;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "id"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "name"), 0);
gda_sql_builder_select_add_target_id (b,
				      gda_sql_builder_add_id (b, "subdata1"),
				      NULL);
sub1 = gda_sql_builder_get_sql_statement (b, FALSE);
g_object_unref (b);

b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "ident"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "lastname"), 0);
gda_sql_builder_select_add_target_id (b,
				      gda_sql_builder_add_id (b, "subdata2"),
				      NULL);
sub2 = gda_sql_builder_get_sql_statement (b, FALSE);
g_object_unref (b);

b = gda_sql_builder_new (GDA_SQL_STATEMENT_COMPOUND);
gda_sql_builder_compound_add_sub_select (b, sub1, TRUE);
gda_sql_builder_compound_add_sub_select (b, sub2, TRUE);
[...]	
g_object_unref (b);	  
	</pre>
<p>
    </p>
</div>
<div class="sect2">
<div class="titlepage"><div><div><h3 class="title">
<a name="id-1.3.3.5.13"></a>SELECT CASE tag WHEN 'Alpha' THEN 1 WHEN 'Bravo' THEN 2 WHEN 'Charlie' THEN 3 ELSE 0 END FROM data</h3></div></div></div>
<p>
	</p>
<pre class="programlisting">
GdaSqlBuilder *b;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
GdaSqlBuilderId id_case = gda_sql_builder_add_case (b,
			                            gda_sql_builder_add_id (b, "tag"),
			                            0,
			                            gda_sql_builder_add_expr (b, NULL, G_TYPE_STRING, "Alpha"),
			                            gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 1),
			                            gda_sql_builder_add_expr (b, NULL, G_TYPE_STRING, "Bravo"),
			                            gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 2),
			                            gda_sql_builder_add_expr (b, NULL, G_TYPE_STRING, "Charlie"),
			                            gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 3),
			                            0);
gda_sql_builder_add_field_value_id (b, id_case, 0);
gda_sql_builder_select_add_target_id (b,
				   gda_sql_builder_add_id (b, "data"),
				   NULL);
[...]	
g_object_unref (b);	  
	</pre>
<p>
    </p>
</div>
<div class="sect2">
<div class="titlepage"><div><div><h3 class="title">
<a name="id-1.3.3.5.14"></a>SELECT product_id, name, sum (4 * 5 * price * 1.200000) FROM invoice_lines</h3></div></div></div>
<p>
	</p>
<pre class="programlisting">
GdaSqlBuilder *b;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_select_add_field (b, "product_id", NULL, NULL);
gda_sql_builder_select_add_field (b, "name", NULL, NULL);

GdaSqlBuilderId op_ids[4], id_function;
op_ids[0] = gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 4);
op_ids[1] = gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 5);
op_ids[2] = gda_sql_builder_add_id (b, "price");
op_ids[3] = gda_sql_builder_add_expr (b, NULL, G_TYPE_FLOAT, 1.2);
id_function = gda_sql_builder_add_function (b, "sum",
                                   gda_sql_builder_add_cond_v (b, GDA_SQL_OPERATOR_TYPE_STAR, op_ids, 4),
                                   0);
gda_sql_builder_add_field_value_id (b, id_function, 0);
gda_sql_builder_select_add_target_id (b,
                                      gda_sql_builder_add_id (b, "invoice_lines"),
				      NULL);
[...]
g_object_unref (b);	  
	</pre>
<p>
    </p>
</div>
<div class="sect2">
<div class="titlepage"><div><div><h3 class="title">
<a name="id-1.3.3.5.15"></a>SELECT id, name, adress, cntry_id, countries.name FROM customers INNER JOIN countries ON (countries.id = cntry_id)</h3></div></div></div>
<p>
	</p>
<pre class="programlisting">
GdaSqlBuilder *b;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
GdaSqlBuilderId t1, t2;
GdaSqlBuilderId id1, id2;
GdaSqlBuilderId jid;
t1 = gda_sql_builder_select_add_target_id (b,
                                           gda_sql_builder_add_id (b, "customers"),
                                           NULL);
t2 = gda_sql_builder_select_add_target_id (b,
                                           gda_sql_builder_add_id (b, "countries"),
                                           NULL);
gda_sql_builder_select_add_field (b, "id", NULL, NULL);
gda_sql_builder_select_add_field (b, "name", NULL, NULL);
gda_sql_builder_select_add_field (b, "adress", NULL, NULL);
id1 = gda_sql_builder_select_add_field (b, "cntry_id", NULL, NULL);
gda_sql_builder_select_add_field (b, "name", "countries", NULL);
id2 = gda_sql_builder_add_field_id (b, "id", "countries");
jid = gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ, id2, id1, 0);
gda_sql_builder_select_join_targets (b, t1, t2, GDA_SQL_SELECT_JOIN_INNER, jid);
[...]
g_object_unref (b);	  
	</pre>
<p>
    </p>
</div>
</div>
<div class="footer">
<hr>Generated by GTK-Doc V1.32</div>
</body>
</html>